package com.neusoft.liduan.d0924;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class JDBC编程增删改 {

	@Test
	public void insert() {
		Connection conn = null;
		//1、准备数据库用户名、密码、url、driver
		String username = "root";
		String password = "root";
		String driver = "com.mysql.jdbc.Driver";
		//协议://地址:端口号/数据库名?连接属性设置
		String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这是属于反射的内容
			//获取数据库连接java.sql.Connection
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		//准备SQL命令
		String sql = "insert into ums_admin(id, username, password) values(0, 'haha', '123')";
		Statement st = null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st = conn.createStatement();
			conn.setAutoCommit(false);
			//执行sql命令
			int row = st.executeUpdate(sql);
			System.out.println(row > 0 ? "添加成功" : "添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			if(st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	@Test
	public void update() {
		Connection conn = null;
		//1、准备数据库用户名、密码、url、driver
		String username = "root";
		String password = "root";
		String driver = "com.mysql.jdbc.Driver";
		//协议://地址:端口号/数据库名?连接属性设置
		String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这是属于反射的内容
			//获取数据库连接java.sql.Connection
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		//准备SQL命令
		String sql = "update ums_admin set password = 'abc' where username = 'haha'";
		Statement st = null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st = conn.createStatement();
			//执行sql命令
			int row = st.executeUpdate(sql);
			System.out.println(row > 0 ? "修改成功" : "修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	@Test
	public void delete() {
		Connection conn = null;
		//1、准备数据库用户名、密码、url、driver
		String username = "root";
		String password = "root";
		String driver = "com.mysql.jdbc.Driver";
		//协议://地址:端口号/数据库名?连接属性设置
		String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这是属于反射的内容
			//获取数据库连接java.sql.Connection
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		//准备SQL命令
		String sql = "delete from ums_admin where username = 'haha'";
		Statement st = null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st = conn.createStatement();
			//执行sql命令
			int row = st.executeUpdate(sql);
			System.out.println(row > 0 ? "删除成功" : "删除失败");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	//练习1，请尝试封装增删改的代码为一个方法
	//练习2，请完成将一个Admin对象入传到增删改方法中，实现增删改功能
}
